package chj.BookInformation;

import chj.Database.StringUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

public class Bookop {
    private String Bno;//图书id
    private String Bname;//图书名
    private String Bpress;//图书出版社
    private String Bauthor;//图书作者
    private String Bcategory;//图书类别
    private int Bnumber;//图书的数量
    public String getBno() {
        return Bno;
    }
    public void setBno(String Bno) {
        this.Bno = Bno;
    }
    public String getBname() {
        return Bname;
    }
    public void setBname(String Bname) {
        this.Bname = Bname;
    }
    public String getBpress() {
        return Bpress;
    }
    public void setBpress(String Bpress) {
        this.Bpress = Bpress;
    }
    public String getBauthor() {
        return Bauthor;
    }
    public void setBauthor(String Bauthor) {
        this.Bauthor = Bauthor;
    }
    public String getBcategory() {
        return Bcategory;
    }
    public void setBcategory(String Bcategory) {
        this.Bcategory = Bcategory;
    }
    public int getBnumber() {
        return Bnumber;
    }
    public void setBnumber(int Bnumber) {
        this.Bnumber = Bnumber;
    }

    public int add(Connection con, Bookop book) throws Exception {

        //插入图书前 检查Bno是否已经存在，若存在，返回0
        String sqlid = "select * from books where Bno = ?";
        PreparedStatement pstmtid = (PreparedStatement) con.prepareStatement(sqlid);
        pstmtid.setString(1, book.getBno());
        ResultSet rs = pstmtid.executeQuery();
        if(rs.next()) {
            return 0;
        }

        String sql = "insert into books values(?,?,?,?,?,?)";
        PreparedStatement pstmt = (PreparedStatement) con.prepareStatement(sql);
        pstmt.setString(1, book.getBno());
        pstmt.setString(2, book.getBname());
        pstmt.setString(3, book.getBauthor());
        pstmt.setString(4, book.getBcategory());
        pstmt.setString(5, book.getBpress());
        //pstmt.setString(5, book.getTypeId());
        pstmt.setInt(6, book.getBnumber());
        return pstmt.executeUpdate();
    }
    //图书查询
    public ResultSet list(Connection con, Bookop bookMessage) throws Exception {
        //将book表和bookType表连接
        //StringBuffer sb = new StringBuffer("select * from book,booktype where book.Bno=booktype.Bno");
        StringBuffer sql = new StringBuffer("select * from books where Bno = Bno ");
        //向sql中添加，并进行模糊查询
        if(StringUtil.isNotEmpty(bookMessage.getBno())) {
            sql.append(" and Bno like '%" + bookMessage.getBno() + "%'");
        }
        //按书名模糊查询
        if(StringUtil.isNotEmpty(bookMessage.getBname())) {
            sql.append(" and Bname like '" + bookMessage.getBname() + "%'");
        }
        //按作者模糊查询
        if(StringUtil.isNotEmpty(bookMessage.getBauthor())) {
            sql.append(" and Bauthor like '%" + bookMessage.getBauthor() + "%'");
        }
        if(StringUtil.isNotEmpty(bookMessage.getBcategory())){
            sql.append("and Bcategory like '%" + bookMessage.getBcategory() + "%'");
        }
        PreparedStatement pstmt=(PreparedStatement) con.prepareStatement(sql.toString());
        return pstmt.executeQuery();
    }

    //查询全部图书
    public ResultSet listAllBooks(Connection con) throws Exception {
        // SQL 查询所有图书
        String sql = "SELECT * FROM books";
        // 使用 PreparedStatement 执行查询
        PreparedStatement pstmt = con.prepareStatement(sql);
        return pstmt.executeQuery(); // 执行查询并返回结果
    }

    //图书删除
    public int delete(Connection con, String id) throws Exception {
        String sql = "delete from books where Bno = ?";
        PreparedStatement pstmt = (PreparedStatement) con.prepareStatement(sql);
        pstmt.setString(1, id);
        return pstmt.executeUpdate();
    }

    //图书修改
    public int update(Connection con, Bookop bookMessage1) throws Exception {
        String sql = "update books set Bname=?, Bauthor=?, Bpress=?, Bcategory = ?, Bnumber = ? where Bno=?";
        PreparedStatement pstmt = (PreparedStatement) con.prepareStatement(sql);
        pstmt.setString(1, bookMessage1.getBname());
        pstmt.setString(2, bookMessage1.getBauthor());
        pstmt.setString(3, bookMessage1.getBpress());
        pstmt.setString(4, bookMessage1.getBcategory());
        pstmt.setInt(5, bookMessage1.getBnumber());
        pstmt.setString(6, bookMessage1.getBno());
        return pstmt.executeUpdate();
    }

    //检查该id的图书是否存在,若存在，返回0，否则返回1
    public ResultSet bookCheck(Connection con, String id,String name) throws Exception{

        //插入图书前 检查Bno是否已经存在，若存在，返回0
        String sqlid = "select * from books where Bno = ? and Bname = ?";
        PreparedStatement pstmtid = (PreparedStatement) con.prepareStatement(sqlid);
        pstmtid.setString(1, id);
        pstmtid.setString(2, name);
        ResultSet rs = pstmtid.executeQuery();
        return rs;
    }
}